package Model;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import Entity.Order;
import Entity.Product;
import Entity.Sale;
import S2.server.ConnectionManage;

public class OrderModel {

	private static Connection conn = ConnectionManage.getInstance()
			.getConnection();
	public static Order addOrder(String orderId,Date date,String clientId, ArrayList<Product> products) throws SQLException{
		String sql = "INSERT INTO order (orderId,date,clientId)"+"VALUES(?,?,?)";
		String sql2;
		ResultSet rs = null;
		Order bean =  new Order();
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setString(1, orderId);
			stmt.setDate(2, (java.sql.Date) date);
			stmt.setString(3, clientId);
			
			rs = stmt.executeQuery();
			

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} 
		sql2="INSERT INTO productinorder(orderId,pId) "+"VALUES(?,?)";
		try (PreparedStatement stmt = conn.prepareStatement(sql2);) {
			for(int i=0;i<products.size();i++)
			{
				stmt.setString(1, orderId);
				stmt.setString(2, products.get(i).getId());
				rs = stmt.executeQuery();
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} 
		
		finally {
			if (rs != null) {
				rs.close();
			}
		}
		return bean;
		
	}
	public static Order UpdateOrder(String orderId,Date date,String clientId,ArrayList<Product> products) throws SQLException {
		String sql = "Update orders set date=? ,clientId=?"
				+ " WHERE orders.orderId=?";
		ResultSet rs = null;
		Order bean =  new Order();

		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setDate(1, date);
			stmt.setString(2,  clientId);
			stmt.setString(3, orderId);
			
			rs = stmt.executeQuery();
	}
	 catch (SQLException e) {
		System.err.println(e);
		return null;
	} 
		String sql2 = "update productinorder set pId=? WHERE productinorder.orderId=?";
		try (PreparedStatement stmt = conn.prepareStatement(sql2);) {
			for(int i=0;i<products.size();i++)
			{
				stmt.setString(1, products.get(i).getId());
				stmt.setString(2, orderId);
				rs = stmt.executeQuery();
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} 
		
		finally {
			if (rs != null) {
				rs.close();
			}
		}
	return bean;
		
	}
public static Order geteOrder(String orderId,Date date,String clientId) throws SQLException {
		
		String sql = "SELECT * FROM" + " order WHERE" + " orderId=?";
		ResultSet rs = null;
		ResultSet rs2 = null;
		Order bean =  new Order();
		
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setString(1, orderId);
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				bean.setDate(rs.getDate(2));
				bean.setClientId(rs.getString(3));;
			} else {
				return null;
			}
		}
			Product prdct=null;
			String sql2 = "SELECT * FROM" + " productinorder WHERE" + " orderId=?";
			try (PreparedStatement stmt1 = conn.prepareStatement(sql2);) {
	
					stmt1.setString(1, orderId);
					rs = stmt1.executeQuery();
				while(rs.next()){
					String sql3 = "SELECT * FROM" + " product WHERE" + " id=?";
					PreparedStatement stmt2 = conn.prepareStatement(sql3);
					stmt2.setString(1, rs.getString(1));
					
					rs2 = stmt2.executeQuery();
					if (rs2.next()) {
						prdct=new Product();
						prdct.setId(rs2.getString(1));
						prdct.setName(rs2.getString(2));
						prdct.setDescription(rs2.getString(3));
						prdct.setPrice(rs2.getString(4));
						prdct.setPicture(rs2.getString(5));
						bean.getProducts().add(prdct);
							}
					}
				}
			
	 catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (rs2 != null) {
				rs2.close();
			}
		}
		return bean;
}
public static void deleteOrder(String orderId) throws SQLException {

	String sql = "DELETE FROM order " +  "WHERE orderId = ?";
	ResultSet rs = null;
	
	try (PreparedStatement stmt = conn.prepareStatement(sql);)
	{		
		stmt.setString(1, orderId);
		rs = stmt.executeQuery();
	}
	 catch (SQLException e) {
			System.err.println(e);
			
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}
}
